package com.ruoyi.web.controller.tool;

import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.RecordProp;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * Excel相关处理
 *
 * @author tangrs
 */
public class ExcelUtilMap {
    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int sheetSize = 65536;
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    /**
     * 数字格式
     */
    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
    /**
     * 工作表名称
     */
    private String sheetName;
    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Excel.Type type;
    /**
     * 工作薄对象
     */
    private Workbook wb;
    /**
     * 工作表对象
     */
    private Sheet sheet;
    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;
    /**
     * 导入导出数据列表
     */
    private List<Map<String, Object>> mapList;
    /**
     * 导入导出数据表头
     */
    private List<RecordProp> propList;
    /**
     * 当前行号
     */
    private int rownum;
    /**
     * 最大高度
     */
    private short maxHeight;
    /**
     * 统计列表
     */
    private Map<Integer, Double> statistics = new HashMap<Integer, Double>();

    /* *********************   导入部分  ***************************/

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流
     * @param propList  字段表集合
     * @return 转换后集合
     */
    public List<Map<String, Object>> importExcelMap(InputStream is, List<RecordProp> propList) throws Exception {
        return importExcelMap(is, 0, propList);
    }

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is       输入流
     * @param titleNum 标题占用行数
     * @param propList  字段表集合
     * @return 转换后集合
     */
    public List<Map<String, Object>> importExcelMap(InputStream is, int titleNum, List<RecordProp> propList) throws Exception {
        return importExcelMap(StringUtils.EMPTY, is, titleNum, propList);
    }

    /**
     * 对excel表单指定表格索引名转换成list
     *
     * @param sheetName 表格索引名
     * @param is        输入流
     * @param titleNum  标题占用行数
     * @param propList  字段表集合
     * @return 转换后集合
     */
    public List<Map<String, Object>> importExcelMap(String sheetName, InputStream is, int titleNum, List<RecordProp> propList) throws Exception {
        //1，取出表头，记录表头名称和列号
        //2，取出表字段
        //3，遍历表头对比表字段描述
        //4，如果对比得上，传入行号，列号获取单元格值，对不上丢弃
        //5，根据表字段类型，强制转换表格值类型，封装到map<prop，value>。
        //6，将该行的map添加到list，返回给调用者
        this.type = Excel.Type.IMPORT;
        this.wb = WorkbookFactory.create(is);
        List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
        // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
        Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
        if (sheet == null) {
            throw new IOException("文件sheet不存在");
        }
        // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
        int rows = sheet.getLastRowNum();
        if (rows > 0) {
            // 定义一个map用于存放excel列的序号和field.
            Map<String, Integer> cellMap = new HashMap<String, Integer>();
            // 1. 获取表头
            Row heard = sheet.getRow(titleNum);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
                Cell cell = heard.getCell(i);
                if (StringUtils.isNotNull(cell)) {
                    String value = this.getCellValue(heard, i).toString();
                    cellMap.put(value, i);
                } else {
                    cellMap.put(null, i);
                }
            }
            // 2.取出表字段
            if (propList.size() == 0) {
                throw new IOException("表不存在或者字段为空！");
            }
            // 从第2行开始取数据,默认第一行是表头.
            for (int i = titleNum + 1; i <= rows; i++) {
                Map<String, Object> map = new HashMap<String, Object>();
                Row row = sheet.getRow(i);
                // 判断当前行是否是空行
                if (isRowEmpty(row)) {
                    continue;
                }
                // 3.遍历prop表字段描述，对比excel表头名称，构造map<prop,value>
                for (RecordProp prop : propList) {
                    if (cellMap.get(prop.getLabel()) != null) {
                        Object val = this.getCellValue(row, cellMap.get(prop.getLabel()));
                        map.put(prop.getProp(), val);
                    }
                }
                listMap.add(map);
            }

        }
        return listMap;
    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public Object getCellValue(Row row, int column) {
        if (row == null) {
            return row;
        }
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (StringUtils.isNotNull(cell)) {
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
                    val = cell.getNumericCellValue();
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                    } else {
                        if ((Double) val % 1 != 0) {
                            val = new BigDecimal(val.toString());
                        } else {
                            val = new DecimalFormat("0").format(val);
                        }
                    }
                } else if (cell.getCellType() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

    /**
     * 判断是否是空行
     *
     * @param row 判断的行
     * @return
     */
    private boolean isRowEmpty(Row row) {
        if (row == null) {
            return true;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    /* *********************   导出部分  ***************************/

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response  返回数据
     * @param mapList   导出数据集合
     * @param sheetName 工作表的名称
     * @param propList  字段表集合
     * @return 结果
     * @throws IOException
     */
    public void exportExcelMap(HttpServletResponse response, List<Map<String, Object>> mapList, String sheetName, List<RecordProp> propList) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        this.init(mapList, sheetName, Excel.Type.EXPORT, propList);
        exportExcelMap(response);
    }

    public void init(List<Map<String, Object>> mapList, String sheetName, Excel.Type type, List<RecordProp> propList) {
        if (mapList == null) {
            mapList = new ArrayList<>();
        }
        this.mapList = mapList;
        this.sheetName = sheetName;
        this.type = type;
        this.propList = propList;
        // 默认创建第一个sheet
        createWorkbook();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    public void exportExcelMap(HttpServletResponse response) {
        try {
            // 核心方法
            writeSheet();
            // 将缓存中创建的Excel文件对象以流的形式写入文件中
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

    /**
     * 创建写入数据到Sheet
     */
    public void writeSheet() {
        // 取出一共有多少个sheet.
        int sheetNo = Math.max(1, (int) Math.ceil(mapList.size() * 1.0 / sheetSize));
        for (int index = 0; index < sheetNo; index++) {
            // 超过65536条数据，会产生两个以上sheet
            createSheet(sheetNo, index);
            // 产生一行，rownum默认为0
            Row row = sheet.createRow(rownum);
            int column = 0;
            // 写入各个字段的列头名称
            for (RecordProp prop : propList) {
                // 创建表头单元格
                this.createCell(prop.getLabel(), row, column++);
            }
            if (Excel.Type.EXPORT.equals(type)) {
                // 第二行开始填入数据
                fillExcelData(index, row);
            }
        }
    }

    /**
     * 创建工作表
     *
     * @param sheetNo sheet数量
     * @param index   序号
     */
    public void createSheet(int sheetNo, int index) {
        // 设置工作表的名称.即sheet名称
        if (sheetNo > 1 && index > 0) {
            this.sheet = wb.createSheet();
            wb.setSheetName(index, sheetName + index);
        }
    }

    /**
     * 创建表头单元格
     */
    public Cell createCell(String attr, Row row, int column) {
        // 创建列
        Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(attr);
        cell.setCellStyle(styles.get("header"));
        return cell;
    }

    /**
     * 填充excel数据
     *
     * @param index 序号
     * @param row   单元格行
     */
    public void fillExcelData(int index, Row row) {
        // 数据量超过65535行数据，换多个sheet装填数据
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, mapList.size());
        for (int i = startNo; i < endNo; i++) {
            // 行数从第二行开始递增
            row = sheet.createRow(i + 1 + rownum - startNo);
            int column = 0;
            // 遍历prop表，取出prop名称，判断map是否存在key
            for (RecordProp prop : propList) {
                // 填充数据
                if (mapList.get(i).get(prop.getProp()) != null) {
                    // 创建cell
                    Cell cell = row.createCell(column++);
                    cell.setCellStyle(styles.get("data" + (2)));
                    cell.setCellValue(mapList.get(i).get(prop.getProp()).toString());
                }else {
                    Cell cell = row.createCell(column++);
                    cell.setCellStyle(styles.get("data" + (2)));
                    cell.setCellValue("");
                }
            }
        }
    }

    /**
     * 创建一个工作簿
     */
    public void createWorkbook() {
        this.wb = new SXSSFWorkbook(500);
        this.sheet = wb.createSheet();
        wb.setSheetName(0, sheetName);
        this.styles = createStyles(wb);
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb) {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        return styles;
    }

}
